#! /usr/local/bin/perl5

use strict;
use warnings ( FATAL => 'all' );

# use lib "../perllib";

use Data::Dumper;
use File::Basename;
use Readonly;
use POSIX qw(strftime);

use FixInc::Logger;
use FixInc::DBAccess;
use FixInc::OopOptsDB;
use Feeds::API::File;
use DateTime;
use IPC::Open3;
use FileHandle;

use constant SCRIPT_NM => basename($0);

Readonly my $HOST_NM => $ENV{HOST} || `hostname | tr -d '\n'`;
Readonly my $NOW => strftime( '%Y%m%d.%H%M%S', localtime );
Readonly my $TODAY => $ENV{TODAY_CYMD} || substr( $NOW, 0, 8 );

# Turn on output autoflush
$| = 1;

# setup log object
my $l = FixInc::Logger->new( min_level => 'notice' );
my $script_nm = SCRIPT_NM;
$l->notice("=== $script_nm has started === (pid=$$) (ppid=@{[getppid()]})");
$l->info( "CMD:", $0, join( " ", @ARGV ) );

## Parse Jobparams ##
my $defs = FixInc::OopOptsDB->newOptSet(
    help => <<'EOH',
This script downloads xml data from oekb web site https://www.profitweb.at
EOH

    defaults => {
        server          => 'HGCDB1',
        web_usr         => 'wdbo_reuters',
        web_retry       => '3',
        delete_fail     => 'y',
        download_days   => '7',
        date_mode       => '1',
        download_mode   => 'D',          ##I=> initial load(both include code tables and none-code tables
    },                                   ##C=> Get code tables' data
                                         ##D=> Get daily update data
                                         ##T=> Get the text data
                                         ##M=> make up the wrong daily updates
                                         
    optdefs => FixInc::OopOptsDB->newOptDef(
        {   name     => 'web_usr',
            aliases  => ['WS'],
            maxargs  => 1,
            required => 1,
            type     => 'string',
            help     => 'The username of HTTPS login.'
        },
        {   name     => 'web_pass',
            aliases  => ['WP'],
            maxargs  => 1,
            required => 1,
            type     => 'string',
            help     => 'The password of the web login user.'
        },
        {   name     => 'web_proxy',
            aliases  => ['WPR'],
            maxargs  => 1,
            required => 0,
            type     => 'string',
            help     => 'The password of the web login user.'
        },
        {   name     => 'web_retry',
            aliases  => ['WR'],
            maxargs  => 1,
            required => 0,
            type     => 'string',
            help     => 'The curl retry times when it encounters errors.'
        },
        {   name     => 'tbl_url',
            aliases  => ['TU'],
            required => 1,
            maxargs  => 1,
            type     => 'string',
            help     => 'The URL of download an Oekb table data.'
        },
        {   name     => 'daily_up_url',
            aliases  => ['DUU'],
            required => 1,
            maxargs  => 1,
            type     => 'string',
            help     => 'The URL of download Oekb daily update data.'
        },
        {   name     => 'email_success',
            aliases  => ['ES'],
            required => 0,
            maxargs  => 9,
            type     => 'string',
            help     => 'email address'
        },
        {   name     => 'email_failure',
            aliases  => ['EF'],
            required => 0,
            maxargs  => 9,
            type     => 'string',
            help     => 'email address'
        },
        {   name     => 'download_mode',
            aliases  => ['DM'],
            required => 1,
            maxargs  => 1,
            type     => 'string',
            help     => 'The download mode: I,C,D.'
        },
        {   name     => 'delete_fail',
            aliases  => ['DF'],
            required => 0,
            maxargs  => 1,
            type     => 'string',
            help     => 'If delete downloaded files when failure'
        },
        {   name     => 'holidays',
            aliases  => ['HO'],
            required => 0,
            maxargs  => 30,
            type     => 'string',
            help     => 'The holidays that do not need to download data from website'
        },
        {   name     => 'download_days',
            aliases  => ['DD'],
            required => 1,
            maxargs  => 1,
            type     => 'string',
            help     => 'How many days data are needed.'
        },
        {   name     => 'date_mode',
            aliases  => ['DAM'],
            required => 1,
            maxargs  => 1,
            type     => 'string',
            help     => 'Use datum or log_datum to download daily updates'
        },
    )
);

my $log = FixInc::Logger->new( min_level => 'info' );

my $oops = FixInc::OopOptsDB->new(
    'optset' => $defs,
    'log'    => $log
    )
    or $l->die("Cannot process options: FixInc::OopOptsDB->new");



my $O = $oops->opthash;
$log->paramApply($O);


# create database handlers
# 2) feed

#print "web_usr=\[", $web_usr, "\]\n";
#print "web_pass=\[", $web_pass, "\]\n";

$log->info("Create $O->{database} db handler ...");
my $feed_dbh = Feeds::API::FeedDB->paramApply($O)->connect( %$O, log => $log )
    or $log->die( "'$script_nm': Cannot connect to '$O->{server}': ",
        $FixInc::DBAccess::errstr );

# get CFA database version
my $fdb_version = Feeds::API::FeedDB->feeddb_version(%$O);

# register job info
my $job = Feeds::API::Job->new( action_type => "oekb_xml_download", log => $log)
    or $log->die("Cannot create new Feeds::API::Job: $!");


print "ooooooooooooooooooooooooooooooooooooo\n";
$log->info(localtime);
# Step 1:get data from table 
my $test_party_id = "0x0000fa0004310ec1";
my @child_party_id = ();
my $count = 0;

#&_recursive_list($test_party_id);

sub _recursive_list
{
    my $party_id_root = shift;
    my $sql = "";
    my $ret = {};

    # Create file handles for in, out and err
    my( $cin, $cout, $cerr) = ( new FileHandle(), new FileHandle(), new FileHandle() );
    
    # Open isql in another process
    open3( $cin, $cout, $cerr, '/usr/pkgs/sybase/OCS/bin/isql', "-w", "500",
        "-S", "HGCDB1", "-U", "icma_feed", "-P", "icma_feed",)
        || $log->die("can not open a process for isql");
        
    # Get child records Columns
    $sql = "use dmo_govcorp
    go
    declare \@level int
    set \@level = 1
    CREATE TABLE #s(
        party_id id_TY not null,
        [level] int null
    )
    CREATE TABLE #tmp(
        party_id id_TY not null, 
        [level] int null
    )
    INSERT INTO #s
    SELECT $party_id_root, 1
    -- find all the children
    while \@level > 0
    begin
        IF EXISTS (SELECT 1 FROM #s WHERE [level] = \@level)
            BEGIN
                SELECT TOP 1 \@party_id=party_id
                FROM #s
                WHERE [level] = \@level
                
                INSERT INTO #tmp
                SELECT \@party_id, \@level

                DELETE FROM #s
                WHERE [level] = \@level 
                AND party_id = \@party_id
                
                INSERT INTO #s
                SELECT party_id, \@level + 1
                FROM party
                WHERE cur_immed_parent_id = \@party_id
                        
                IF \@\@rowcount > 0 SET \@level= \@level + 1
            END
        ELSE
            BEGIN
                SET \@level = \@level - 1
            END
    end
    --DROP table #tmp
    --DROP table #s
    go
    ";
    $log->debug($sql);
	my $sth = $feed_dbh->prepare($sql);
	$sth->execute;
    # print Dumper $sql;
    # print $cin $sql;
    # print $sql;
    close( $cin );
}
$log->info(localtime);
$log->info($count);
print "ooooooooooooooooooooooooooooooooooooo\n";

print "xxxxx binary_searchbinary_searchbinary_searchbinary_search xxxxx\n";
$log->info(localtime);
my @array = qw/a b c d e f g h i j k l m n o p q r s t u v w x y z/;
my $word = "r";
my $index = binary_search(\@array, $word);# Invoke our algorithm
if (defined $index) {
	print "$word occurs at position $index.\n";
} else {
	print "$word doesn't occur.\n";
}
# $index = binary_search( \@array, $word )
# @array is a list of lowercase strings in alphabetical order.
# $word is the target word that might be in the list.
# binary_search() returns the array index such that $array[$index]
# is $word.
sub binary_search {
	my ($array, $word) = @_;
	my ($low, $high) = ( 0, @$array - 1 );
	while ( $low <= $high ) { # While the window is open
		my $try = int( ($low+$high) /2 ); # Try the middle element
		$low = $try+1, next if $array->[$try] lt $word; # Raise bottom
		$high = $try-1, next if $array->[$try] gt $word; # Lower top
		return $try; # We've found the word!
	}
	return; # The word isn't there.
}
print "xxxxx binary_searchbinary_searchbinary_searchbinary_search xxxxx\n";

$job->success();

# to force DESTROY
undef $job;
$feed_dbh->disconnect() if defined $feed_dbh;
$log->notice("=== @{[SCRIPT_NM]} has stopped ===");

__END__
